package com.library.dao;

import com.library.entity.Book;
import com.library.util.C3p0Utils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BookManageDao {

    private final QueryRunner queryRunner = new QueryRunner(C3p0Utils.getDataSource());

    /**
     * 根据ID获取书籍详情
     * @param id 书籍ID
     * @return 书籍对象
     */
    public Book getBookById(int id) {
        String sql = "SELECT * FROM book WHERE id = ?";
        try {
            return queryRunner.query(sql, new BeanHandler<>(Book.class), id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 根据ISBN获取书籍
     * @param isbn ISBN号
     * @return 书籍对象
     */
    public Book getBookByISBN(String isbn) {
        String sql = "SELECT * FROM book WHERE ISBN = ?";
        try {
            return queryRunner.query(sql, new BeanHandler<>(Book.class), isbn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 检查ISBN是否已存在
     * @param isbn ISBN号
     * @return 是否存在
     */
    public boolean isISBNExists(String isbn) {
        String sql = "SELECT COUNT(*) FROM book WHERE ISBN = ?";
        try {
            Long count = queryRunner.query(sql, new ScalarHandler<Long>(), isbn);
            return count != null && count > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 添加新书籍
     * @param book 书籍对象
     * @return 是否添加成功
     */
    public boolean addBook(Book book) {
        String sql = "INSERT INTO book (ISBN, bname, author, publisher, introduction, pubDate, clcNum, bookStatus) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        try {
            int result = queryRunner.update(sql, 
                book.getISBN(), 
                book.getBname(), 
                book.getAuthor(), 
                book.getPublisher(), 
                book.getIntroduction(), 
                book.getPubDate(), 
                book.getClcNum(), 
                book.getBookStatus()
            );
            return result > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    /**
     * 更新书籍信息（不允许修改ISBN）
     * @param book 书籍对象
     * @return 是否更新成功
     */
    public boolean updateBook(Book book) {
        String sql = "UPDATE book SET bname = ?, author = ?, publisher = ?, introduction = ?, pubDate = ?, clcNum = ? WHERE id = ?";
        try {
            int result = queryRunner.update(sql, 
                book.getBname(), 
                book.getAuthor(), 
                book.getPublisher(), 
                book.getIntroduction(), 
                book.getPubDate(), 
                book.getClcNum(), 
                book.getId()
            );
            return result > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    /**
     * 删除书籍（设置为下架状态，同时注销相关副本）
     * @param id 书籍ID
     * @return 是否删除成功
     */
    public boolean deleteBook(int id) {
        try {
            // 开始事务
            queryRunner.getDataSource().getConnection().setAutoCommit(false);
            
            // 首先获取书籍的ISBN
            String getIsbnSql = "SELECT ISBN FROM book WHERE id = ?";
            String isbn = queryRunner.query(getIsbnSql, new ScalarHandler<String>(), id);
            
            if (isbn == null) {
                queryRunner.getDataSource().getConnection().rollback();
                queryRunner.getDataSource().getConnection().setAutoCommit(true);
                return false;
            }
            
            // 更新书籍状态为下架
            String updateBookSql = "UPDATE book SET bookStatus = 0 WHERE id = ?";
            int bookResult = queryRunner.update(updateBookSql, id);
            
            // 注销该书籍的所有副本（除了已借出的副本）
            String updateCopiesSql = "UPDATE bookcopy SET status = 2 WHERE ISBN = ? AND status != 0";
            queryRunner.update(updateCopiesSql, isbn);
            
            // 提交事务
            queryRunner.getDataSource().getConnection().commit();
            queryRunner.getDataSource().getConnection().setAutoCommit(true);
            
            return bookResult > 0;
        } catch (SQLException e) {
            try {
                // 回滚事务
                queryRunner.getDataSource().getConnection().rollback();
                queryRunner.getDataSource().getConnection().setAutoCommit(true);
            } catch (SQLException rollbackEx) {
                rollbackEx.printStackTrace();
            }
            e.printStackTrace();
            return false;
        }
    }

    /**
     * 检查书籍是否有副本正在借出
     * @param bookId 书籍ID
     * @return 是否有副本正在借出
     */
    public boolean hasBorrowedCopies(int bookId) {
        String sql = "SELECT COUNT(*) FROM bookcopy bc " +
                    "JOIN book b ON bc.ISBN = b.ISBN " +
                    "WHERE b.id = ? AND bc.status = 0";
        try {
            Long count = queryRunner.query(sql, new ScalarHandler<Long>(), bookId);
            return count != null && count > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    /**
     * 批量删除书籍（设置为下架状态，同时注销相关副本）
     * @param ids 书籍ID数组
     * @return 是否删除成功
     */
    public boolean batchDeleteBooks(int[] ids) {
        if (ids == null || ids.length == 0) {
            return false;
        }

        try {
            // 开始事务
            queryRunner.getDataSource().getConnection().setAutoCommit(false);
            
            // 获取所有要删除书籍的ISBN
            StringBuilder getIsbnSql = new StringBuilder("SELECT ISBN FROM book WHERE id IN (");
            for (int i = 0; i < ids.length; i++) {
                getIsbnSql.append("?");
                if (i < ids.length - 1) {
                    getIsbnSql.append(",");
                }
            }
            getIsbnSql.append(")");
            
            Object[] params = new Object[ids.length];
            for (int i = 0; i < ids.length; i++) {
                params[i] = ids[i];
            }
            
            java.util.List<String> isbns = queryRunner.query(getIsbnSql.toString(), 
                new org.apache.commons.dbutils.handlers.ColumnListHandler<String>("ISBN"), params);
            
            // 更新书籍状态为下架
            StringBuilder updateBookSql = new StringBuilder("UPDATE book SET bookStatus = 0 WHERE id IN (");
            for (int i = 0; i < ids.length; i++) {
                updateBookSql.append("?");
                if (i < ids.length - 1) {
                    updateBookSql.append(",");
                }
            }
            updateBookSql.append(")");
            
            int bookResult = queryRunner.update(updateBookSql.toString(), params);
            
            // 注销相关副本（除了已借出的副本）
            if (!isbns.isEmpty()) {
                StringBuilder updateCopiesSql = new StringBuilder("UPDATE bookcopy SET status = 2 WHERE status != 0 AND ISBN IN (");
                for (int i = 0; i < isbns.size(); i++) {
                    updateCopiesSql.append("?");
                    if (i < isbns.size() - 1) {
                        updateCopiesSql.append(",");
                    }
                }
                updateCopiesSql.append(")");
                
                queryRunner.update(updateCopiesSql.toString(), isbns.toArray());
            }
            
            // 提交事务
            queryRunner.getDataSource().getConnection().commit();
            queryRunner.getDataSource().getConnection().setAutoCommit(true);
            
            return bookResult > 0;
        } catch (SQLException e) {
            try {
                // 回滚事务
                queryRunner.getDataSource().getConnection().rollback();
                queryRunner.getDataSource().getConnection().setAutoCommit(true);
            } catch (SQLException rollbackEx) {
                rollbackEx.printStackTrace();
            }
            e.printStackTrace();
            return false;
        }
    }

    /**
     * 检查ISBN是否已存在（排除指定ID的书籍）
     * @param isbn ISBN号
     * @param excludeId 要排除的书籍ID
     * @return 是否存在
     */
    public boolean isISBNExistsExcludeId(String isbn, int excludeId) {
        String sql = "SELECT COUNT(*) FROM book WHERE ISBN = ? AND id != ?";
        try {
            Long count = queryRunner.query(sql, new ScalarHandler<Long>(), isbn, excludeId);
            return count != null && count > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 更新书籍信息（包括ISBN，同时更新相关副本的ISBN）
     * @param book 书籍对象
     * @param oldISBN 原来的ISBN
     * @return 是否更新成功
     */
    public boolean updateBookWithISBN(Book book, String oldISBN) {
        String updateBookSql = "UPDATE book SET ISBN = ?, bname = ?, author = ?, publisher = ?, introduction = ?, pubDate = ?, clcNum = ? WHERE id = ?";
        String updateCopiesSql = "UPDATE bookcopy SET ISBN = ? WHERE ISBN = ?";
        
        try {
            // 开始事务
            queryRunner.getDataSource().getConnection().setAutoCommit(false);
            
            // 更新book表
            int bookResult = queryRunner.update(updateBookSql, 
                book.getISBN(), 
                book.getBname(), 
                book.getAuthor(), 
                book.getPublisher(), 
                book.getIntroduction(), 
                book.getPubDate(), 
                book.getClcNum(), 
                book.getId()
            );
            
            // 如果ISBN发生了变化，更新相关副本的ISBN
            if (!book.getISBN().equals(oldISBN)) {
                queryRunner.update(updateCopiesSql, book.getISBN(), oldISBN);
            }
            
            // 提交事务
            queryRunner.getDataSource().getConnection().commit();
            queryRunner.getDataSource().getConnection().setAutoCommit(true);
            
            return bookResult > 0;
        } catch (SQLException e) {
            try {
                // 回滚事务
                queryRunner.getDataSource().getConnection().rollback();
                queryRunner.getDataSource().getConnection().setAutoCommit(true);
            } catch (SQLException rollbackEx) {
                rollbackEx.printStackTrace();
            }
            e.printStackTrace();
            return false;
        }
    }

    /**
     * 物理删除书籍
     * @param id 书籍ID
     * @return 是否删除成功
     */
    public boolean physicalDeleteBook(int id) {
        String sql = "DELETE FROM book WHERE id = ?";
        try {
            int result = queryRunner.update(sql, id);
            return result > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    /**
     * 恢复书籍（设置为上架状态，同时恢复相关副本）
     * @param id 书籍ID
     * @return 是否恢复成功
     */
    public boolean restoreBook(int id) {
        try {
            // 开始事务
            queryRunner.getDataSource().getConnection().setAutoCommit(false);
            
            // 首先获取书籍的ISBN
            String getIsbnSql = "SELECT ISBN FROM book WHERE id = ?";
            String isbn = queryRunner.query(getIsbnSql, new ScalarHandler<String>(), id);
            
            if (isbn == null) {
                queryRunner.getDataSource().getConnection().rollback();
                queryRunner.getDataSource().getConnection().setAutoCommit(true);
                return false;
            }
            
            // 更新书籍状态为上架
            String updateBookSql = "UPDATE book SET bookStatus = 1 WHERE id = ?";
            int bookResult = queryRunner.update(updateBookSql, id);
            
            // 恢复该书籍的所有注销副本为可借状态（status从2改为1）
            String updateCopiesSql = "UPDATE bookcopy SET status = 1 WHERE ISBN = ? AND status = 2";
            queryRunner.update(updateCopiesSql, isbn);
            
            // 提交事务
            queryRunner.getDataSource().getConnection().commit();
            queryRunner.getDataSource().getConnection().setAutoCommit(true);
            
            return bookResult > 0;
        } catch (SQLException e) {
            try {
                // 回滚事务
                queryRunner.getDataSource().getConnection().rollback();
                queryRunner.getDataSource().getConnection().setAutoCommit(true);
            } catch (SQLException rollbackEx) {
                rollbackEx.printStackTrace();
            }
            e.printStackTrace();
            return false;
        }
    }


    /**
     * 获取所有已上架的书籍列表（用于添加副本时的下拉框选择）
     * @return 已上架的书籍列表
     */
    public List<Book> getAvailableBooks() {
        String sql = "SELECT ISBN as ISBN, bname as bname, author as author, publisher as publisher, introduction as introduction, pubDate as pubDate, clcNum as clcNum, bookStatus as bookStatus FROM book WHERE bookStatus = 1 ORDER BY bname";
        try {
            return queryRunner.query(sql, new BeanListHandler<>(Book.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return new ArrayList<>();
    }
    /**
     * 检查指定ISBN的书籍是否存在且已上架
     * @param isbn ISBN号
     * @return 是否可用于创建副本
     */
    public boolean isBookAvailableForCopy(String isbn) {
        String sql = "SELECT COUNT(*) FROM book WHERE ISBN = ? AND bookStatus = 1";
        try {
            Long count = queryRunner.query(sql, rs -> {
                if (rs.next()) {
                    return rs.getLong(1);
                }
                return 0L;
            }, isbn);
            return count != null && count > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
} 